package com.cs.paoyer.administration.business.entity;

//import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
//import java.util.HashMap;
import java.util.Vector;
import java.sql.PreparedStatement;
//import java.util.Iterator;

import com.cs.paoyer.administration.business.model.UserVO;
//import com.cs.paoyer.common.util.Encrypter;
//import java.security.NoSuchAlgorithmException;
//
//import com.cs.paoyer.common.exception.UserException;
//import com.cs.paoyer.common.exception.SystemException;

/**
 * DAO para crear, consultar y actualizar registros en la tabla USUARIOS.
 * 
 * @author Nicolas.Ceron@consultsoft.com
 * @version 1.0
 */

public class UserDAO 
{

  /**
   * Conexion a la base de datos.
   */
  private Connection conn = null;

  /**
   * Constructor que inicializa la conexion con la base de datos.
   * @param conn Conexion con la base de datos.
   */
  
  public UserDAO( Connection conn ) {
    this.conn = conn;
  }

  /**
   * TABLA TCS_USUARIO
   */

    
	/*public UserVO authenticateUser(UserVO criterea) throws SQLException 
    {
    
    String query = "SELECT usua.* FROM tcs_usuario usua WHERE usua.nombre='"
			           	+ criterea.getName()+"' AND usua.campo_a1='"+criterea.getPassword()+"'";
    Statement stm = null;
    ResultSet rst = null;
		stm = conn.createStatement();
		rst = stm.executeQuery(query);
		UserVO user = null;
		if( rst.next() ) 
		{
				user = new UserVO();
				user.setCode(new Long(rst.getLong( "codigo")));      
				user.setName(rst.getString( "nombre"));
				user.setDescription(rst.getString( "descripcion"));      
        user.setPassword(rst.getString( "campo_a1"));          
	 }
   return user;
  }*/
  
  public UserVO authenticateUser(UserVO criterea) throws SQLException 
    {
    
    String query =
    "SELECT usua.codigo usua_codigo, usua.nombre usua_nombre, "+
    "usua.descripcion usua_descripcion, usua.campo_a1 usua_password, "+
      "null autr_codigo, gus.descripcion gus_descripcion, gus.codigo gus_codigo, usua.campo_n1 " +
    "FROM tcs_usuario usua, tcs_grupo_usuario gus " +
    "WHERE usua.nombre = '"+ criterea.getName()+"' "+
    "AND gus.codigo= usua.gus_codigo";
    
    Statement stm = null;
    ResultSet rst = null;
		stm = conn.createStatement();
		rst = stm.executeQuery(query);
		UserVO user = null;
		if( rst.next() ) 
		{
				user = new UserVO();
				user.setCode(new Long(rst.getLong( "usua_codigo")));      
				user.setName(rst.getString( "usua_nombre"));
				user.setDescription(rst.getString( "usua_descripcion"));      
        user.setPassword(rst.getString( "usua_password"));    
        user.setUserGroupCode(new Long(rst.getLong("gus_codigo")));
        user.setUserGroupName(rst.getString("gus_descripcion"));
        user.setCodeArea(new Long(rst.getLong("campo_n1")));
	 }
   return user;
  }

    public Collection findUserByCriterea(UserVO criterea) throws SQLException 
    {
    String query = "SELECT usu.codigo,"+
                       "       usu.nombre,"+
                       "       to_char(usu.desde_vigencia,'yyyy-mm-dd') dvig,"+
                       "       to_char(usu.hasta_vigencia,'yyyy-mm-dd') hvig,"+
                       "       usu.vigencia, "+
                       "       usu.campo_a1, "+
                       "       nvl(usu.campo_n1,0)campo_n1, "+
                       "       usu.fecha_crea,"+
                       "       usu.usuario_crea,"+
                       "       usu.terminal_crea,"+
                       "       usu.fecha_modifica,"+
                       "       usu.usuario_modifica,"+
                       "       usu.terminal_modifica,"+
                       "       usu.descripcion,"+
                       "       usu.gus_codigo,"+
                       "       gus.nombre userGroupName,"+
                       "       area.NOMBRE areaName "+
                       " FROM tcs_usuario usu, tcs_grupo_usuario gus, beneficios.TCS_BEN_AREA area";
   

    String filters = "";
    // Filtro por Nombre
    if ( (criterea.getName() != null) && !(criterea.getName().equalsIgnoreCase("")) ) {
      filters = filters + " (UPPER(usu.nombre) LIKE '%" + criterea.getName().toUpperCase() + "%' OR UPPER(usu.descripcion) LIKE '%" + criterea.getDescription().toUpperCase() + "%') " ;
    }
    

    /*// Filtro por Descripcion
    if ( (criterea.getDescription() != null) && !(criterea.getDescription().equalsIgnoreCase("")) ) 
		{
				if ( filters.equalsIgnoreCase("") ) 
				{
						filters = filters + " UPPER(usu.descripcion) LIKE '%" + criterea.getDescription().toUpperCase() + "%' ";
				}
				else 
				{
						filters = filters + " AND UPPER(usu.descripcion) LIKE '%" + criterea.getDescription().toUpperCase() + "%' ";
				}
    }*/
    

    // Filtro por Codigo
    if ( (criterea.getCode() != null) && !(criterea.getCode().longValue()==0) ) 
		{
				if ( filters.equalsIgnoreCase("") ) 
				{
						filters = filters + " usu.codigo = " + criterea.getCode();
				}
				else 
				{
						filters = filters + " AND usu.codigo = " + criterea.getCode();
				}
    }
    
    //filtro por codigo area
     if ( (criterea.getCodeArea() != null) && !(criterea.getCodeArea().longValue()==0) ) 
		{
				if ( filters.equalsIgnoreCase("") ) 
				{
						filters = filters + " area.codigo = " + criterea.getCodeArea();
				}
				else 
				{
						filters = filters + " AND area.codigo = " + criterea.getCodeArea();
				}
    }
    
    
    if ( (criterea.getUserGroupCode() != null) && !(criterea.getUserGroupCode().longValue()==0) ) 
		{
				if ( filters.equalsIgnoreCase("") ) 
				{
						filters = filters + " gus.codigo = " + criterea.getUserGroupCode();
				}
				else 
				{
          if(criterea.getUserGroupCode() != null)
          {
            filters = filters + " AND gus.codigo = " + criterea.getUserGroupCode();
          }
						
				}
    }
    
    // Si hay filtros, entonces armar la clausula WHERE 
    if ( !filters.equalsIgnoreCase("") ) {
      query = query + " where usu.campo_a1 IS NULL and gus.campo_a1 IS NULL and usu.gus_codigo = gus.codigo and area.codigo (+)= usu.campo_n1 and usu.VIGENCIA = 'S' and " + filters;
    }
    else
    {
      query = query + " where usu.campo_a1 IS NULL and gus.campo_a1 IS NULL and usu.gus_codigo = gus.codigo and area.codigo (+)= usu.campo_n1 and usu.VIGENCIA = 'S' ";
    }
    
    //System.out.println(query);
    String striquery=query;
    Statement stm = null;
    ResultSet rst = null;
    ArrayList users = new ArrayList();
		stm = conn.createStatement();
		rst = stm.executeQuery(query);
		UserVO user = null;
		while ( rst.next() ) 
		{
				user = new UserVO();
				user.setCode(new Long(rst.getLong( "codigo")));      
				user.setName(rst.getString( "nombre"));
				user.setDescription(rst.getString( "descripcion"));
        user.setPassword(rst.getString( "campo_a1"));
        user.setCodeArea(new Long(rst.getLong("campo_n1")));
        user.setEffectiveFrom(rst.getString("dvig"));
				user.setEffectiveUntil(rst.getString("hvig"));
				user.setUse(rst.getString("vigencia"));
        user.setUserGroupCode(new Long(rst.getLong("gus_codigo")));
				user.setCreationUser(rst.getString("usuario_crea"));
				user.setCreationTerminal(rst.getString("terminal_crea"));
        user.setCreationDate(rst.getString("fecha_crea"));
				user.setUpdateUser(rst.getString("usuario_modifica"));
				user.setUpdateTerminal(rst.getString("terminal_modifica"));
        user.setUpdateDate(rst.getString("fecha_modifica"));       
        user.setUserGroupName(rst.getString("userGroupName"));
        user.setNameArea(rst.getString("areaName"));
				users.add(user);
				user=null;
		}
  	rst.close();
        stm.close();
    return users;
  }
  
    public Collection findUserByCritereaAdmin(UserVO criterea) throws SQLException 
    {
    String query = "SELECT usu.codigo,"+
                       "       usu.nombre,"+
                       "       to_char(usu.desde_vigencia,'yyyy-mm-dd') dvig,"+
                       "       to_char(usu.hasta_vigencia,'yyyy-mm-dd') hvig,"+
                       "       usu.vigencia, "+
                       "       usu.campo_a1, "+
                       "       nvl(usu.campo_n1,0)campo_n1, "+
                       "       usu.fecha_crea,"+
                       "       usu.usuario_crea,"+
                       "       usu.terminal_crea,"+
                       "       usu.fecha_modifica,"+
                       "       usu.usuario_modifica,"+
                       "       usu.terminal_modifica,"+
                       "       usu.descripcion,"+
                       "       usu.gus_codigo,"+
                       "       gus.nombre userGroupName,"+
                       "       area.NOMBRE areaName "+
                       " FROM tcs_usuario usu, tcs_grupo_usuario gus, beneficios.TCS_BEN_AREA area";
    

    String filters = "";
    // Filtro por Nombre
    if ( (criterea.getName() != null) && !(criterea.getName().equalsIgnoreCase("")) ) {
      filters = filters + " (UPPER(usu.nombre) LIKE '%" + criterea.getName().toUpperCase() + "%' OR UPPER(usu.descripcion) LIKE '%" + criterea.getDescription().toUpperCase() + "%') " ;
    }
    

    // Filtro por Codigo
    if ( (criterea.getCode() != null) && !(criterea.getCode().longValue()==0) ) 
                {
                                if ( filters.equalsIgnoreCase("") ) 
                                {
                                                filters = filters + " usu.codigo = " + criterea.getCode();
                                }
                                else 
                                {
                                                filters = filters + " AND usu.codigo = " + criterea.getCode();
                                }
    }
    
    //filtro por codigo area
     if ( (criterea.getCodeArea() != null) && !(criterea.getCodeArea().longValue()==0) ) 
                {
                                if ( filters.equalsIgnoreCase("") ) 
                                {
                                                filters = filters + " area.codigo = " + criterea.getCodeArea();
                                }
                                else 
                                {
                                                filters = filters + " AND area.codigo = " + criterea.getCodeArea();
                                }
    }
    
    
    if ( (criterea.getUserGroupCode() != null) && !(criterea.getUserGroupCode().longValue()==0) ) 
                {
                                if ( filters.equalsIgnoreCase("") ) 
                                {
                                                filters = filters + " gus.codigo = " + criterea.getUserGroupCode();
                                }
                                else 
                                {
          if(criterea.getUserGroupCode() != null)
          {
            filters = filters + " AND gus.codigo = " + criterea.getUserGroupCode();
          }
                                                
                                }
    }
    
    // Si hay filtros, entonces armar la clausula WHERE 
    if ( !filters.equalsIgnoreCase("") ) {
      query = query + " where usu.campo_a1 IS NULL and gus.campo_a1 IS NULL and usu.gus_codigo = gus.codigo and area.codigo (+)= usu.campo_n1 and " + filters;
    }
    else
    {
      query = query + " where usu.campo_a1 IS NULL and gus.campo_a1 IS NULL and usu.gus_codigo = gus.codigo and area.codigo (+)= usu.campo_n1  ";
    }
    
    //System.out.println(query);
    Statement stm = null;
    ResultSet rst = null;
    ArrayList users = new ArrayList();
                stm = conn.createStatement();
                rst = stm.executeQuery(query);
                UserVO user = null;
                while ( rst.next() ) 
                {
                                user = new UserVO();
                                user.setCode(new Long(rst.getLong( "codigo")));      
                                user.setName(rst.getString( "nombre"));
                                user.setDescription(rst.getString( "descripcion"));
                                user.setPassword(rst.getString( "campo_a1"));
                                user.setCodeArea(new Long(rst.getLong("campo_n1")));
                                user.setEffectiveFrom(rst.getString("dvig"));
                                user.setEffectiveUntil(rst.getString("hvig"));
                                user.setUse(rst.getString("vigencia"));
                                user.setUserGroupCode(new Long(rst.getLong("gus_codigo")));
                                user.setCreationUser(rst.getString("usuario_crea"));
                                user.setCreationTerminal(rst.getString("terminal_crea"));
                                user.setCreationDate(rst.getString("fecha_crea"));
                                user.setUpdateUser(rst.getString("usuario_modifica"));
                                user.setUpdateTerminal(rst.getString("terminal_modifica"));
                                user.setUpdateDate(rst.getString("fecha_modifica"));       
                                user.setUserGroupName(rst.getString("userGroupName"));
                                user.setNameArea(rst.getString("areaName"));
                                users.add(user);
                                user=null;
                }
        rst.close();
                stm.close();
    return users;
    }
  
  public boolean createUser( UserVO user ) throws SQLException {
  
				String strSQL="";
				boolean result=false;
				PreparedStatement ps=null;
							 strSQL = "" +
								 "INSERT INTO tcs_usuario ("     +
								 "            codigo, nombre, descripcion, campo_a1, campo_n1, desde_vigencia, hasta_vigencia, "   +
								 "            vigencia,  gus_codigo, vfl_codigo,"   +
								 "            usuario_crea,terminal_crea, fecha_crea,usuario_modifica," +
                 "             terminal_modifica,fecha_modifica) "  +
								 "            VALUES(scs_usuario.nextval,?,?,?,?,to_date(?,'yyyy-mm-dd'),to_date(?,'yyyy-mm-dd'),?,?,2,?,?,?,?,?,?)";
                 
        
				ps = conn.prepareStatement( strSQL );
				ps.setString(1, user.getName().toUpperCase());
				ps.setString(2, user.getDescription().toUpperCase());
				ps.setString(3, user.getPassword());
        ps.setString(4,(user.getCode()).toString());
        ps.setString(5, user.getEffectiveFrom());
				ps.setString(6, user.getEffectiveUntil());
				ps.setString(7, user.getUse().toUpperCase());
        ps.setLong(8, user.getUserGroupCode().longValue());
				ps.setString(9, user.getCreationUser().toUpperCase());
				ps.setString(10, user.getCreationTerminal());
        ps.setString(11, user.getCreationDate());
				ps.setString(12, user.getUpdateUser().toUpperCase());
				ps.setString(13,user.getUpdateTerminal());
        ps.setString(14, user.getUpdateDate());         
				result = ps.execute();
				ps.close();  
				return result;
		}
    
    public boolean updateUser( UserVO user ) throws SQLException {
  
				String strSQL="";
				boolean result=false;
				PreparedStatement ps=null;
							 strSQL = "" +
								 "UPDATE paoyer.tcs_usuario SET "
                 +"           nombre=?,"+
                 "            descripcion=?,"+
                 "            campo_a1=?,"+
                 "            campo_n1=?,"+
                 "            desde_vigencia=to_date(?,'yyyy-mm-dd'),"+
                 "            hasta_vigencia=to_date(?,'yyyy-mm-dd'), "   +
								 "            vigencia=?,"+
                 "            gus_codigo=?,"+
                 "            usuario_modifica=?," +
                 "            terminal_modifica=?"+
                 "            where codigo=?";
                 
        
				ps = conn.prepareStatement( strSQL );
				ps.setString(1, user.getName().toUpperCase());
				ps.setString(2, user.getDescription().toUpperCase());
				ps.setString(3, user.getPassword());
        ps.setString(4, (user.getCodeArea()).toString());
        ps.setString(5, user.getEffectiveFrom());
				ps.setString(6, user.getEffectiveUntil());
				ps.setString(7, user.getUse().toUpperCase());
        ps.setLong(8, user.getUserGroupCode().longValue());
				ps.setString(9, user.getUpdateUser().toUpperCase());
				ps.setString(10, "WEB=" + user.getUpdateTerminal());
        ps.setLong(11, user.getCode().longValue());
				result = ps.execute();
				ps.close();  
				return result;
		}
    
    public boolean deleteUser( UserVO user ) throws SQLException 
    {
    
            String strSQL = "delete from tcs_usuario where codigo = ?";

            PreparedStatement ps = conn.prepareStatement( strSQL );
            
            ps.setLong(1, user.getCode().longValue());

            boolean update = ps.execute();
            ps.close();
            return update;
    
    }
    
    public boolean activateUser( UserVO user ) throws SQLException 
    {
    
            String strSQL = "update tcs_usuario set vigencia = ? where codigo = ?";

            PreparedStatement ps = conn.prepareStatement( strSQL );
            ps.setString(1, user.getUse());
            ps.setLong(2, user.getCode().longValue());

            boolean update = ps.execute();
            ps.close();
            return update;
    }
    
    public boolean desactivateUser( UserVO user ) throws SQLException 
    {
    
            String strSQL = "update tcs_usuario set vigencia = ? where codigo = ?";

            PreparedStatement ps = conn.prepareStatement( strSQL );
            ps.setString(1, user.getUse());
            ps.setLong(2, user.getCode().longValue());

            boolean update = ps.execute();
            ps.close();
            return update;
    }
    
    public UserVO userGroup(UserVO criterea) throws SQLException 
    {
    
    String query = "SELECT grupo.codigo CODIGO "+
                    "FROM paoyer.TCS_GRUPO_USUARIO grupo,paoyer.TCS_USUARIO usuario "+
                    "WHERE usuario.GUS_CODIGO = grupo.CODIGO "+
                    "AND usuario.NOMBRE = '"+criterea.getName()+"'";    
    Statement stm = null;
    ResultSet rst = null;
		stm = conn.createStatement();
		rst = stm.executeQuery(query);
		UserVO group=criterea;
		if( rst.next() ) 
		{
				group.setUserGroupCode(new Long(rst.getLong("codigo")));      
	 }
   return group;
  }
  
  public Vector getResources(UserVO criterea)
  {
    Vector resources = new Vector();
    String query = "SELECT o1.CAMPO_A4 resources "+
                   "FROM TCS_OPCION o1, TCS_OPCIONES_GRUPO g "+
                   "WHERE g.opc_codigo = o1.codigo "+
                   "AND o1.tipo_opcion = 'W' "+
                   "AND o1.CAMPO_A4 IS NOT NULL "+
                   "AND g.GUS_CODIGO = "+criterea.getUserGroupCode();
    Statement stm = null;
    ResultSet rst = null;
    try{
      stm = conn.createStatement();
      rst = stm.executeQuery(query);
      while( rst.next() ) 
      {
        resources.add(rst.getString("resources"));
      }
    }catch(SQLException e)
    {
		System.err.println("/******** TDSUB - cwsub");
		e.printStackTrace();
		System.err.println("TDSUB - cwsub ********/");
    }
    return resources;
  }
  

  public String getMenu(String strUserGroup, String strpath)
  {
    MenuDAO mn =  new MenuDAO(strUserGroup,conn,strpath);
    return mn.getMenu();
  }
}